

DROP INDEX IF EXISTS trconfig_run;
DROP TABLE IF EXISTS trconfig;
DROP INDEX IF EXISTS trnotes_run;
DROP TABLE IF EXISTS trnotes;
DROP INDEX IF EXISTS testrun_case;
DROP TABLE IF EXISTS testrun CASCADE;
DROP INDEX IF EXISTS steps_case;
DROP TABLE IF EXISTS steps CASCADE;
DROP INDEX IF EXISTS testcase_name;
DROP TABLE IF EXISTS testcase CASCADE;
DROP TYPE IF EXISTS tctype CASCADE;
DROP TYPE IF EXISTS trstatus CASCADE;

CREATE TYPE tctype AS ENUM ('tc','fl');
CREATE TYPE trstatus AS ENUM ('PASS', 'FAIL', 'NRUN', 'IFIF');

create table testcase (
	tc_num serial PRIMARY KEY,
	tc_name varchar not null,
	tc_summary varchar not null,
	tc_desc varchar default '',
	tc_type tctype not null default 'tc',
	tc_del boolean not null default FALSE,
	tc_parent integer not null references testcase(tc_num)
);
CREATE INDEX testcase_name ON testcase(tc_name,tc_del);
-- needed to seed the first entery in db
INSERT INTO testcase VALUES ( 0, 'TestCases','The top testcase','','fl',FALSE,0);  

CREATE TABLE testrun (
	tr_num serial PRIMARY KEY,
	tr_status trstatus not null,
	tr_date date not null default current_date,
	tr_tester varchar(32) not null,
	tr_kbuild integer not null,
	tr_pbuild integer not null,
	tr_cluster varchar default '',
	tr_quick varchar(24) default '',
	tr_bug varchar(32) default '',
	tr_dump varchar(64) default '',
	tr_start timestamp,
	tr_stop timestamp,
	tr_intr smallint not null default 7,
	tr_del boolean not null default FALSE,
	tr_testcase integer not null references testcase(tc_num)
);
CREATE INDEX testrun_case ON testrun (tr_testcase,tr_del,tr_intr);

CREATE TABLE trnotes (
	trn_num serial PRIMARY KEY,
	trn_notes varchar NOT NULL,
	trn_del boolean NOT NULL DEFAULT FALSE,
	trn_testrun integer NOT NULL REFERENCES testrun(tr_num)
);
CREATE UNIQUE INDEX trnotes_run ON trnotes (trn_testrun,trn_del);

CREATE TABLE trconfig (
	trc_num serial PRIMARY KEY,
	trc_program varchar DEFAULT NULL, 
	trc_config varchar NOT NULL,
	trc_del boolean NOT NULL DEFAULT FALSE,
	trc_testrun integer NOT NULL REFERENCES testrun(tr_num)
);
CREATE UNIQUE INDEX trconfig_run ON trconfig (trc_testrun,trc_del,trc_program);


CREATE TABLE steps (
	st_num serial PRIMARY KEY,
	st_stnum integer NOT NULL DEFAULT 0,
	st_desc varchar NOT NULL,
	st_del boolean NOT NULL DEFAULT FALSE,
	st_testcase integer NOT NULL references testcase(tc_num)
);
CREATE INDEX steps_case ON steps(st_testcase,st_del);

CREATE OR REPLACE FUNCTION deltc( integer ) RETURNS INTEGER AS $$
DECLARE
	pnum ALIAS FOR $1;
	rec testcase%ROWTYPE;
BEGIN
    UPDATE testcase SET tc_del = 'TRUE' WHERE tc_num = pnum;
    UPDATE testcase SET tc_del = 'TRUE' WHERE tc_parent = pnum;
    FOR rec IN SELECT * FROM testcase  WHERE tc_parent = pnum LOOP
		RAISE NOTICE 'Parent %',rec;
		IF rec.tc_type = 'fl' THEN
			PERFORM deltc ( rec.tc_num);
		END IF;
    END LOOP;
	RETURN rec.tc_num;
END;
$$ LANGUAGE 'plpgsql' STRICT;

-- testing data
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'FIT','Functional Tests','fl',0);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'SIT','System Tests','fl',0);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'ext5','ext5','fl',1);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'Limits Testing','Limit Tests','fl',3);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'Quota','Quota Tests','fl',3);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'Snapshots','Snapshot Tests','fl',3);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'Workload Stability','Stability Tests','fl',3);  

INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( '1Node Misc','1 node Tests','fl',7);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'HTTP','HTTP Tests','fl',7);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'iozone','iozone Tests','fl',7);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'Mixed','Mixed Tests','fl',7);
INSERT INTO testcase (tc_name,tc_summary,tc_type,tc_parent) VALUES ( 'postmark','postmark Tests','fl',7);

INSERT INTO testcase (tc_name,tc_summary,tc_parent) VALUES ( 'User Quota','User quota',5);  
INSERT INTO testcase (tc_name,tc_summary,tc_parent) VALUES ( 'Group Quota','Group quota',5);  
INSERT INTO testcase (tc_name,tc_summary,tc_parent) VALUES ( '250000 Postmark','Write 250000 files to one directory using postmark',12);  
INSERT INTO testcase (tc_name,tc_summary,tc_parent) VALUES ( 'File System Size','Create a 7T volume using all the disk space on MSA',4);

-- testrun table
INSERT INTO testrun (tr_status,tr_date,tr_tester,tr_kbuild,tr_pbuild,tr_bug,tr_cluster,tr_testcase) VALUES
	('FAIL','2009-05-28','shaun.savage',68,68,'28640','bb37s7-8',3);
INSERT INTO testrun (tr_status,tr_date,tr_tester,tr_kbuild,tr_pbuild,tr_cluster,tr_quick,tr_testcase) VALUES
	('FAIL','2009-05-28','shaun.savage',68,68,'bb37s7-8','something',4);
INSERT INTO trnotes (trn_testrun, trn_notes) VALUES	(1,'test two test on same day');
INSERT INTO testrun (tr_status,tr_date,tr_tester,tr_kbuild,tr_pbuild,tr_cluster,tr_testcase) VALUES
	('PASS','2009-05-28','shaun.savage',68,68,'bb37s7-8',4);
INSERT INTO trnotes (trn_testrun, trn_notes) VALUES	(2,'Used my script to create the volume');
INSERT INTO testrun (tr_status,tr_date,tr_tester,tr_kbuild,tr_pbuild,tr_bug,tr_cluster,tr_testcase) VALUES
	('FAIL','2009-05-27','shaun.savage',68,68,'28567','bb36s7-8',4);
INSERT INTO trnotes (trn_testrun, trn_notes) VALUES	(3,'Simplicty failed, it uses 32 int not 64 long for value');

